
Imports System
Imports System.Collections
Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient
Imports C3I.CodegenTools


Public Class MySQLDataSchema
    Inherits DataSchema

    Dim cmd As MySqlCommand, conn As MySqlConnection

    ''' <summary>
    ''' Build server connection string from object properties
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub BuildConnectionString()
        _ConnectionString = "Data Source=" & _Server & ";Database=" & _DatabaseName & ";User ID=" & _UserID & ";Password=" & _Password ';Command Logging=false

        '
    End Sub


    ''' <summary>
    ''' Populate table information 
    ''' </summary>
    ''' <remarks></remarks>
    Public Overrides Sub PopulateTableInfo()
        conn = New MySqlConnection

        BuildConnectionString()

        conn.ConnectionString = _ConnectionString
        conn.Open()

        PopulateDataTypes()
        ' PopulateDescriptions()
        PopulatePrimaryKeys()
        'PopulateForeignKeys()
        PopulateFields()
    End Sub

    ''' <summary>
    ''' Get .NET datatypes from table
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateDataTypes()
        'get  some data (any data) from source table
        cmd = New MySqlCommand("select * from `" & _TableName & "` limit 1", conn)
        reader = cmd.ExecuteReader
        _columnTypes.Clear()

        'get the .NET datatypes for each column
        For nF = 0 To reader.FieldCount - 1
            _columnTypes.Add(reader.GetName(nF), reader.GetFieldType(nF).Name)
        Next

        reader.Close()
    End Sub
    ''' <summary>
    ''' Get column descriptions (if supported)
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateDescriptions()
        'populate array with field descriptions for specified table
        cmd = New MySqlCommand("SELECT objname,value FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', '" & _TableName & "', 'column', default)", conn)
        reader = cmd.ExecuteReader
        _columnDescriptions.Clear()

        Do While reader.Read
            _columnDescriptions.Add(reader.GetString(0), reader.GetString(1))
        Loop

        reader.Close()
    End Sub
    ''' <summary>
    ''' Get primary keys for specific table
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulatePrimaryKeys()
        'get Primary keys
        strSQL = "Select k.column_name " & vbCrLf
        strSQL = strSQL & "FROM information_schema.table_constraints t " & vbCrLf
        strSQL = strSQL & "                      JOIN information_schema.key_column_usage k " & vbCrLf
        strSQL = strSQL & "                      USING(constraint_name,table_schema,table_name) " & vbCrLf
        strSQL = strSQL & "WHERE t.constraint_type='PRIMARY KEY' AND t.table_schema='" & Me.DatabaseName & "' AND t.table_name='" & Me.TableName & "'; "

        'populate array with primary keys for specified table
        cmd = New MySqlCommand(strSQL, conn)
        reader = cmd.ExecuteReader
        _primaryKeys.Clear()
        Do While reader.Read
            _primaryKeys.Add(reader.GetString(0), reader.GetString(0))
        Loop

        reader.Close()

    End Sub
    ''' <summary>
    ''' Get foreign keys for specific table
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateForeignKeys()

        'build SQL to get foreign keys
        'get Primary keys
        strSQL = "Select Case k.column_name " & vbCrLf
        strSQL = strSQL & "FROM information_schema.table_constraints t " & vbCrLf
        strSQL = strSQL & "                      JOIN information_schema.key_column_usage k " & vbCrLf
        strSQL = strSQL & "                      USING(constraint_name,table_schema,table_name) " & vbCrLf
        strSQL = strSQL & "WHERE t.constraint_type='FOREIGN KEY' AND t.table_schema='" & Me.DatabaseName & "' AND t.table_name='" & Me.TableName & "'; "

        'populate array with foreign keys for specified table
        cmd = New MySqlCommand(strSQL, conn)
        reader = cmd.ExecuteReader
        _foreignKeys.Clear()

        Do While reader.Read

            If Not ForeignKeys.ContainsKey(reader.GetString(0)) Then
                Dim foreignKeyarray(2) As String
                foreignKeyarray(1) = reader.GetString(1)
                foreignKeyarray(2) = reader.GetString(2)
                _foreignKeys.Add(reader.GetString(0), foreignKeyarray)

            End If
        Loop

        reader.Close()

    End Sub
    ''' <summary>
    ''' Get table columns 
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateFields()
        Dim nF As Integer, columndictionary As Dictionary(Of String, String)
        'get INFORMATION_SCHEMA layout for table
        strSQL = "SELECT     COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE,  " & vbCrLf
        strSQL = strSQL & "        COLLATION_NAME, COLUMN_DEFAULT, IS_NULLABLE, 'False' As ISIDENTITY " & vbCrLf
        strSQL = strSQL & "FROM         INFORMATION_SCHEMA.COLUMNS AS COLS " & vbCrLf
        strSQL = strSQL & "WHERE     (TABLE_NAME = '" & _TableName & "') AND  table_schema='test' ORDER BY ORDINAL_POSITION" & vbCrLf

        cmd = New MySqlCommand(strSQL, conn)

        reader = cmd.ExecuteReader

        Do While reader.Read
            columndictionary = New Dictionary(Of String, String)()
            'populate dictionary with column properties 
            For nF = 0 To reader.FieldCount - 1
                columndictionary.Add(reader.GetName(nF), reader.GetValue(nF).ToString)
            Next

            _columnsList.Add(columndictionary)

        Loop

        reader.Close()



    End Sub
    ''' <summary>
    ''' Get databases for specified server
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetDatabases() As Generic.List(Of String)
        Dim conn As New MySqlConnection, databaseList As New Generic.List(Of String)
        Dim reader As MySqlDataReader = Nothing, dcmd As MySqlCommand, rowCount As Integer
        Dim ProgressBar As New frmProgressBar

        'set connection string
        conn.ConnectionString = ConnectionString

        Try
            conn.Open()
            'get all databases from server
            dcmd = New MySqlCommand("SHOW DATABASES;", conn)

            reader = dcmd.ExecuteReader

        Catch e As Exception

        End Try

        rowCount = 0
        Dim tableName As String
        databaseList.Clear()

        'get a list of databases to process 
        Do While reader.Read
            tableName = reader(0).ToString

            'ignore any system tables
            If tableName <> "information_schema" And tableName <> "mysql" Then
                rowCount = rowCount + 1
                databaseList.Add(tableName)
            End If
        Loop

        GetDatabases = databaseList

    End Function
    ''' <summary>
    ''' Get tables for specified database
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetTables() As Generic.List(Of DBObject)
        Dim conn2 As New MySqlConnection() ', ConnectionString As String
        Dim reader As MySqlDataReader, dcmd As MySqlCommand, tableList As New Generic.List(Of DBObject)

        conn2.ConnectionString = ConnectionString
        conn2.Open()


        dcmd = New MySqlCommand("show tables", conn2)
        reader = dcmd.ExecuteReader
        tableList.Clear()

        Do While reader.Read

            'tableList.Add(reader(0).ToString)
        Loop

        conn2.Close()
        GetTables = tableList

    End Function

    ''' <summary>
    ''' Get stored procedure parameters
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateParameters()
        ' Dim nF As Integer, columndictionary As Dictionary(Of String, String)



    End Sub

    ''' <summary>
    ''' Get stored procedures
    ''' </summary>
    ''' <returns>List of db objects</returns>
    ''' <remarks></remarks>
    Public Overrides Function GetStoredProcedures() As Generic.List(Of DBObject)
        Dim spList As New Generic.List(Of DBObject)


        GetStoredProcedures = spList

    End Function


    Overrides Sub PopulateIndexes()

    End Sub

    ''' <summary>
    ''' Get generic data type based on native database type 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetDataType(ByVal strNativeType As String) As String
        GetDataType = ""
    End Function

    ''' <summary>
    ''' Get generic column data type based on native database column type 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetGenericColumnType(ByVal strNativeType As String) As String
        GetGenericColumnType = ""
    End Function

    Overrides Sub CheckIfView()

    End Sub

End Class
